﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using LumenWorks.Framework.IO.Csv;
using System.IO; 
using LumenWorks.Framework.Tests.Unit.IO.Csv;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Adex;
using MediaMonitor.Common;
using System.Transactions;
using System.Drawing;
using System.Xml;
using System.Xml.Linq;

namespace MediaMonitor.Admin
{
    public partial class RF_Upload : Adex.BasePage
    {


        protected void Page_Load(object sender, EventArgs e)
        {
            //if (flAjaxUploader.IsPosting)
            //    this.ManagePost();
        }

        private void ManagePost()
        {
            //HttpPostedFileAJAX pf = flAjaxUploader.PostedFile;

            ////if (pf.ContentType.Equals("image/gif") && pf.ContentLength <= 5 * 1024)
            //    flAjaxUploader.SaveAs("~/temp", pf.FileName);
        }

        protected void btnUpload_Click(Object sender, EventArgs e)
        {

            string errMsg1, errMsg2, errMsg3, errMsg4, errMsg5, errMsg6, errMsg7;
            errMsg1 = errMsg2 = errMsg3 = errMsg4 = errMsg5 = errMsg6 = errMsg7 = "";


            if (flRF1.PostedFile.FileName.Length > 0)
            {
                lblInfo1.ForeColor = UploadRF(flRF1, out errMsg1, chkPriliminaryData.Checked) == true ? Color.Blue : Color.Red;
                lblInfo1.Text = errMsg1;
            }
            else
                lblInfo1.Text = "No file selected to upload";


            if (flRF2.PostedFile.FileName.Length > 0)
            {
                lblInfo2.ForeColor = UploadRF(flRF2, out errMsg2,chkPriliminaryData.Checked) == true ? Color.Blue : Color.Red;
                lblInfo2.Text = errMsg2;
            }
            else
                lblInfo2.Text = "No file selected to upload";

            if (flRF3.PostedFile.FileName.Length > 0)
            {
                lblInfo3.ForeColor = UploadRF(flRF3, out errMsg3, chkPriliminaryData.Checked) == true ? Color.Blue : Color.Red;
                lblInfo3.Text = errMsg3;
            }
            else
                lblInfo3.Text = "No file selected to upload";

            if (flRF4.PostedFile.FileName.Length > 0)
            {
                lblInfo4.ForeColor = UploadRF(flRF4, out errMsg4, chkPriliminaryData.Checked) == true ? Color.Blue : Color.Red;
                lblInfo4.Text = errMsg4;
            }
            else
                lblInfo4.Text = "No file selected to upload";

            if (flRF5.PostedFile.FileName.Length > 0)
            {
                lblInfo5.ForeColor = UploadRF(flRF5, out errMsg5, chkPriliminaryData.Checked) == true ? Color.Blue : Color.Red;
                lblInfo5.Text = errMsg5;
            }
            else
                lblInfo5.Text = "No file selected to upload";

            if (flRF6.PostedFile.FileName.Length > 0)
            {
                lblInfo6.ForeColor = UploadRF(flRF6, out errMsg6, chkPriliminaryData.Checked) == true ? Color.Blue : Color.Red;
                lblInfo6.Text = errMsg6;
            }
            else
                lblInfo6.Text = "No file selected to upload";

            if (flRF7.PostedFile.FileName.Length > 0)
            {
                lblInfo7.ForeColor = UploadRF(flRF7, out errMsg7, chkPriliminaryData.Checked) == true ? Color.Blue : Color.Red;
                lblInfo7.Text = errMsg7;
            }
            else
                lblInfo7.Text = "No file selected to upload";


            pnlInfo1.Visible = pnlInfo2.Visible = pnlInfo3.Visible = pnlInfo4.Visible = pnlInfo5.Visible = pnlInfo6.Visible = pnlInfo7.Visible = true;

            pnlUpload.Visible = false;


        }

        private bool UploadRF(FileUpload flRF, out string errMsg, bool priliminaryData)
        {

            byte countryID = MediaMonitor.Common.AppUser.GetCountry();

            StreamReader readFile = new StreamReader(flRF.PostedFile.InputStream);
            //FileInfo fl = new FileInfo(Server.MapPath("~/temp/" + pf.FileName));
            DateTime rfDate = DateTime.Now.AddYears(50);


            CsvReader csv = new CsvReader(new StringReader(readFile.ReadToEnd()), false, ',', '"', '\\', '☺', ValueTrimmingOptions.All); // for comments char = 122


            DataTable dt = GetRFDataTable();
            if (csv.FieldCount != dt.Columns.Count - 7)
            {
                errMsg = "<h2>Upload Failed!!!</h2>Not a valid RF file. Field length is different";
                return false;
            }

            int currentRow = 0;
            int currentCol = 0;

            try
            {


                //fill data from RF file
                while (csv.ReadNextRecord())
                {

                    currentRow += 1;

                    DataRow dr = dt.NewRow();
                    for (int i = 0; i < dt.Columns.Count - 7; i++)
                    {
                        currentCol = i + 1;

                        switch (dt.Columns[i + 1].DataType.Name)
                        {
                            case "DateTime":
                                dr[i + 1] = Utility.IsNullDate(Utility.GetDateFromNumber(csv[i].ToString()));
                                break;
                            case "Int32":
                                dr[i + 1] = Utility.IsNullInt(csv[i]);
                                break;
                            case "String":
                                dr[i + 1] = Utility.IsNullStr(csv[i]);
                                break;
                            case "Boolean":
                                dr[i + 1] = Utility.IsNullBool(csv[i]);
                                break;
                            case "Decimal":
                                dr[i + 1] = Utility.IsNullDec(csv[i]);
                                break;
                            case "Double":
                                dr[i + 1] = Utility.IsNullDec(csv[i]);
                                break;
                        }//end swtich


                    }
                    int mediaCode = Utility.IsNullInt(csv[0]);

                    int mediaTypeCode = MediaMonitor.Common.App.GetMediaTypeCode(mediaCode);
                    dr[36] = mediaTypeCode;
                    string mediaType = "";

                    switch (mediaTypeCode)
                    {
                        case 1:
                        case 3:
                        case 5:
                            {
                                dr[37] = "TVCR";
                                break;
                            }
                        case 6:
                            {
                                dr[37] = "PR";
                                break;
                            }
                        case 7:
                            {
                                dr[37] = "OD";
                                break;
                            }
                        case 8:
                            {
                                dr[37] = "NS";
                                break;
                            }
                    }
                    dr[38] = Utility.GetTimeFromNumbers(Utility.IsNullStr(csv[2].ToString(), "000000"));
                    dr[39] = Utility.GetTimeFromNumbers(Utility.IsNullStr(csv[3].ToString(), "000000"));
                    dr[40] = countryID;
                    dr[41] = priliminaryData;
                    dt.Rows.Add(dr);
                }

            }
            catch (Exception ex1)
            {

                errMsg = "<h2>Upload Failed!!!</h2>Failed to shift data from CSV to DB. CSV Row: " + currentRow + ", Col: " + currentCol + "." + ex1.Message;
                return false;
            }
            TransactionOptions options = new TransactionOptions();
            options.Timeout = new TimeSpan(0, 2, 0);
            options.IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted;
            rfDate = Convert.ToDateTime(dt.Rows[0]["RFDate"]);
            using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Suppress, options))
            {
                using (SubSonic.SharedDbConnectionScope conn = new SubSonic.SharedDbConnectionScope(System.Configuration.ConfigurationManager.ConnectionStrings["DBAdex"].ToString()))
                {
                    try
                    {
                        rfDate = Convert.ToDateTime(dt.Rows[0]["RFDate"]);
                        RfController.DeleteAllCommercialByDate(rfDate, countryID);
                        TblCommercialLogRFDate.Delete("RFDate", rfDate);
                    }
                    catch (Exception ex1)
                    {
                        ts.Dispose();
                        conn.Dispose();

                        errMsg = "<h2>Upload Failed!!!</h2>Failed to Delete previous Commercial records of same date.<Br>" + ex1.Message;
                        return false;
                    }
                }
            }
            TblCommercialLogRFDate obj1 = new TblCommercialLogRFDate();
            obj1.RFDate = rfDate.AddHours(4).ToString();
            obj1.PriliminaryData = priliminaryData;
            obj1.Save();
            dt.TableName = "nonu";
            if (System.IO.File.Exists(Server.MapPath("~/nonu.xml")) == true)
            {
                System.IO.File.Delete(Server.MapPath("~/nonu.xml"));
            }
            dt.WriteXml(Server.MapPath("~/nonu.xml"));

            var xDocument = XDocument.Load(Server.MapPath("~/nonu.xml"));
            // convert the xml into string (did not get why do you want to do this)
            string xml = xDocument.ToString();
            SPs.SpTest2(xml).Execute();
            System.IO.File.Delete(Server.MapPath("~/nonu.xml"));
            //TransactionOptions options = new TransactionOptions();
            //options.Timeout = new TimeSpan(0, 2, 0);
            //options.IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted;

            //DateTime rfDate = DateTime.Now.AddYears(50);

            //using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Suppress, options))
            //{
            //    using (SubSonic.SharedDbConnectionScope conn = new SubSonic.SharedDbConnectionScope(System.Configuration.ConfigurationManager.ConnectionStrings["DBAdex"].ToString()))
            //    {

            //        //transaction activities.
            //        try
            //        {
            //            //delete all the CommercialLog of same RFDate
            //            try
            //            {
            //                rfDate = Convert.ToDateTime(dt.Rows[0]["RFDate"]);
            //                RfController.DeleteAllCommercialByDate(rfDate, countryID);
            //            }
            //            catch (Exception ex1)
            //            {
            //                ts.Dispose();
            //                conn.Dispose();

            //                errMsg = "<h2>Upload Failed!!!</h2>Failed to Delete previous Commercial records of same date.<Br>" + ex1.Message;
            //                return false;
            //            }


            //            foreach (DataRow dr in dt.Rows)
            //            {

            //                rfDate = Utility.IsNullDate(dr["RFDate"], DateTime.MinValue);

            //                int mediaCode = Utility.IsNullInt(dr["MediaCode"]);

            //                int mediaTypeCode = MediaMonitor.Common.App.GetMediaTypeCode(mediaCode);

            //                string mediaType = "";

            //                switch (mediaTypeCode)
            //                {
            //                    case 1:
            //                    case 3:
            //                    case 5:
            //                        {
            //                            mediaType = "TVCR";
            //                            break;
            //                        }
            //                    case 6:
            //                        {
            //                            mediaType = "PR";
            //                            break;
            //                        }
            //                    case 7:
            //                        {
            //                            mediaType = "OD";
            //                            break;
            //                        }
            //                    case 8:
            //                        {
            //                            mediaType = "NS";
            //                            break;
            //                        }
            //                }


           

            // } //end loop dt

            //            ts.Complete();

            //            ts.Dispose();

            //        }//end try

            //        catch (Exception ex2)
            //        {
            //            ts.Dispose();
            //            conn.Dispose();

            //            try
            //            {
            //                RfController.DeleteAllCommercialByDate(rfDate, MediaMonitor.Common.AppUser.GetCountry());
            //            }
            //            catch { }

            //            errMsg = "<h2>System fails to upload data process</h2>No data uploaded, Please try again. <br> " + ex2.Message;
            //            return false;

            //        }

            //    }//end using SharedDBConnection
            //}//end using TransactionScope

            errMsg = "<h2>Data Uploaded Successfully</h2>All data from RF file successfully uploaded";
            return true;

        }

        private System.Data.DataTable GetRFDataTable()
        {
            DataTable dt = new DataTable();
            //// dt = Adex.Rf.Query().WHERE("ID", 0).ExecuteDataSet().Tables[0];
            dt.Columns.Add("ID", System.Type.GetType("System.Int32"));//0
            dt.Columns.Add("MediaCode", System.Type.GetType("System.Int32"));//1
            dt.Columns.Add("RFDate", System.Type.GetType("System.DateTime"));//2
            dt.Columns.Add("TimeStart", System.Type.GetType("System.Int32"));//3
            dt.Columns.Add("TimeEnd", System.Type.GetType("System.Int32"));//4
            dt.Columns.Add("Duration", System.Type.GetType("System.Int32"));//5
            dt.Columns.Add("ProgramCode", System.Type.GetType("System.Int32"));//6
            dt.Columns.Add("ProgramName", System.Type.GetType("System.String"));//7
            dt.Columns.Add("Typology", System.Type.GetType("System.String"));//8
            dt.Columns.Add("NominalCost", System.Type.GetType("System.Decimal"));//9
            dt.Columns.Add("RealCost", System.Type.GetType("System.Decimal"));//10

            dt.Columns.Add("PositionInBreak", System.Type.GetType("System.Int32"));//11
            dt.Columns.Add("NumberOfSpotsInBreak", System.Type.GetType("System.Int32"));//12
            dt.Columns.Add("TypeOfSpot", System.Type.GetType("System.Int32"));//13
            dt.Columns.Add("TypeOfBuyCode", System.Type.GetType("System.Int32"));//14
            dt.Columns.Add("TypeOfBuyName", System.Type.GetType("System.String"));//15

            dt.Columns.Add("ProdVersionCode", System.Type.GetType("System.Int32"));//16
            dt.Columns.Add("ProdVersionName", System.Type.GetType("System.String"));//17
            dt.Columns.Add("ProductCode", System.Type.GetType("System.Int32"));//18
            dt.Columns.Add("ProductName", System.Type.GetType("System.String"));//19

            dt.Columns.Add("Constant1", System.Type.GetType("System.String"));//20
            dt.Columns.Add("Constant2", System.Type.GetType("System.String"));//21

            dt.Columns.Add("CategoryCode", System.Type.GetType("System.Int32"));//22
            dt.Columns.Add("CategoryName", System.Type.GetType("System.String"));//23

            dt.Columns.Add("SubSectorCode", System.Type.GetType("System.Int32"));//24
            dt.Columns.Add("SubSectorName", System.Type.GetType("System.String"));//25

            dt.Columns.Add("SectorCode", System.Type.GetType("System.Int32"));//26
            dt.Columns.Add("SectorName", System.Type.GetType("System.String"));//27

            dt.Columns.Add("Constant3", System.Type.GetType("System.String"));//28

            dt.Columns.Add("BrandCode", System.Type.GetType("System.Int32"));//29
            dt.Columns.Add("BrandName", System.Type.GetType("System.String"));//30

            dt.Columns.Add("AdvertiserCode", System.Type.GetType("System.Int32"));//31
            dt.Columns.Add("AdvertiserName", System.Type.GetType("System.String"));//32

            dt.Columns.Add("Constant4", System.Type.GetType("System.String"));//33

            dt.Columns.Add("AgencyCode", System.Type.GetType("System.Int32"));//34
            dt.Columns.Add("AgencyName", System.Type.GetType("System.String"));//35
            dt.Columns.Add("mediaTypeCode", System.Type.GetType("System.Int32"));//36
            dt.Columns.Add("mediaType", System.Type.GetType("System.String"));//37
            dt.Columns.Add("TimeStartFormatted", System.Type.GetType("System.String"));//38
            dt.Columns.Add("TimeEndFormatted", System.Type.GetType("System.String"));//39
            dt.Columns.Add("CountryID", System.Type.GetType("System.Int32"));//40
            dt.Columns.Add("PriliminaryData", System.Type.GetType("System.Boolean"));//41
            
            return dt;

        }


    }
}